package com.realshare.frame.util.excel;

import com.realshare.frame.util.excel.common.Callback;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;

import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
 * 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况
 * ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new
 * FileInputStream("c:\\test.xlsx"); Workbook wb=new XSSFWorkbook(file);
 * 使用方式：
 * ExcelUtils excelUtils = new ExcelUtils() {
 *
 * @Override public void commit(List<List<String>> sheetList, boolean isCommit) {
 * //执行导入处理
 * }
 * };
 * excelUtils.readExcel("d:\\test.xlsx", 1);
 * //excelUtils.readExcel("d:\\test.xls", "sheet1");
 */
public class ReadExcel {

    private static final Logger LOGGER = LoggerFactory.getLogger(ReadExcel.class);

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";
    public static final String POINT = ".";
    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    //单次回调的最大值
    private final int callbackSize = 1000;

    private boolean first = true;

    private Callback callback;

    public ReadExcel() {
    }

    public ReadExcel(Callback callback) {
        this.callback = callback;
    }

    public void setCallback(Callback callback) {
        this.callback = callback;
    }

    /**
     * The type of the data value is indicated by an attribute on the cell. The
     * value is usually in a "v" element within the cell.
     */
    private enum xssfDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
    }

    /**
     * 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
     * <p/>
     * Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
     * http://www.ecma-international.org/publications/standards/Ecma-376.htm
     * <p/>
     * A web-friendly version is http://openiso.org/Ecma/376/Part4
     */
    private class XSSFSheetHandler extends DefaultHandler {

        /**
         * Table with styles
         */
        private StylesTable stylesTable;

        /**
         * Table with unique strings
         */
        private ReadOnlySharedStringsTable sharedStringsTable;

        // Set when V start element is seen
        private boolean vIsOpen;

        // Set when cell start element is seen;
        // used when cell close element is seen.
        private xssfDataType nextDataType;

        // Used to format numeric cell values.
        private short formatIndex;
        private String formatString;
        private final DataFormatter formatter;

        private int thisColumn = -1;
        // The last column printed to the output stream
        private int lastColumnNumber = -1;

        // Gathers characters as they are seen.
        private StringBuffer value;
        private List<String> record;
        private List<List<String>> rows = new ArrayList<List<String>>();

        /**
         * Accepts objects needed while parsing.
         *
         * @param styles  Table of styles
         * @param strings Table of shared strings
         */
        XSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings) {
            this.stylesTable = styles;
            this.sharedStringsTable = strings;
            this.value = new StringBuffer();
            this.nextDataType = xssfDataType.NUMBER;
            this.formatter = new DataFormatter();
            record = new ArrayList<String>();
            rows.clear();// 每次读取都清空行集合
        }

        /*
         * (non-Javadoc)
         *
         * @see
         * org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
         * java.lang.String, java.lang.String, org.xml.sax.Attributes)
         */
        public void startElement(String uri, String localName, String name,
                                 Attributes attributes) throws SAXException {

            if ("inlineStr".equals(name) || "v".equals(name)) {
                vIsOpen = true;
                // Clear contents cache
                value.setLength(0);
            }
            // c => cell
            else if ("c".equals(name)) {
                // Get the cell reference
                String r = attributes.getValue("r");
                int firstDigit = -1;
                for (int c = 0; c < r.length(); ++c) {
                    if (Character.isDigit(r.charAt(c))) {
                        firstDigit = c;
                        break;
                    }
                }
                thisColumn = nameToColumn(r.substring(0, firstDigit));

                // Set up defaults.
                this.nextDataType = xssfDataType.NUMBER;
                this.formatIndex = -1;
                this.formatString = null;
                String cellType = attributes.getValue("t");
                String cellStyleStr = attributes.getValue("s");
                if ("b".equals(cellType)) {
                    nextDataType = xssfDataType.BOOL;
                } else if ("e".equals(cellType)) {
                    nextDataType = xssfDataType.ERROR;
                } else if ("inlineStr".equals(cellType)) {
                    nextDataType = xssfDataType.INLINESTR;
                } else if ("s".equals(cellType)) {
                    nextDataType = xssfDataType.SSTINDEX;
                } else if ("str".equals(cellType)) {
                    nextDataType = xssfDataType.FORMULA;
                } else if (cellStyleStr != null) {
                    // It's a number, but almost certainly one
                    // with a special style or format
                    int styleIndex = Integer.parseInt(cellStyleStr);
                    XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                    this.formatIndex = style.getDataFormat();
                    this.formatString = style.getDataFormatString();
                    if (this.formatString == null) {
                        this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
                    }
                }
            }

        }

        /*
         * (non-Javadoc)
         *
         * @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
         * java.lang.String, java.lang.String)
         */
        public void endElement(String uri, String localName, String name) throws SAXException {
            String thisStr;
            // v => contents of a cell
            if ("v".equals(name)) {
                // Process the value contents as required.
                // Do now, as characters() may be called more than once
                switch (nextDataType) {

                    case BOOL:
                        char first = value.charAt(0);
                        thisStr = first == '0' ? "FALSE" : "TRUE";
                        break;

                    case ERROR:
                        thisStr = "\"ERROR:" + value.toString() + '"';
                        break;

                    case FORMULA:
                        // A formula could result in a string value,
                        // so always add double-quote characters.
                        thisStr = value.toString();
                        break;

                    case INLINESTR:
                        // TODO: have seen an example of this, so it's untested.
                        XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                        thisStr = rtsi.toString();
                        break;

                    case SSTINDEX:
                        String sstIndex = value.toString();
                        int idx = Integer.parseInt(sstIndex);
                        XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
                        thisStr = rtss.toString();
                        break;

                    case NUMBER:
                        String n = value.toString();
                        // 判断是否是日期格式
                        if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
                            Double d = Double.parseDouble(n);
                            Date date = HSSFDateUtil.getJavaDate(d);
                            thisStr = formatDateToString(date);
                        } else if (formatString != null) {
                            thisStr = formatter.formatRawCellContents(Double.parseDouble(n), formatIndex, formatString);
                        } else {
                            thisStr = n;
                        }
                        if (NumberUtils.isNumber(n)) {
                            Number number = Float.parseFloat(n);
                            int i = number.intValue();
                            double d = Double.parseDouble(n);
                            //如果是整数,可能会带小数，例如1.00，去掉小数
                            if (i == d) {
                                thisStr = n + "";
                            }
                        }
                        break;

                    default:
                        thisStr = "";
                        break;
                }

                // Output after we've seen the string contents
                // Emit commas for any fields that were missing on this row
                if (lastColumnNumber == -1) {
                    lastColumnNumber = 0;
                }
                //判断内容为空的情况
                if (record.size() < thisColumn) {
                    for (int i = record.size(); i < thisColumn; i++) {
                        record.add(i, "");
                    }
                }
                record.add(thisColumn, thisStr);
                // Update column
                if (thisColumn > -1) {
                    lastColumnNumber = thisColumn;
                }
            } else if ("row".equals(name)) {
                // Print out any missing commas if needed
                // Columns are 0 based
                if (lastColumnNumber == -1) {
                    lastColumnNumber = 0;
                }
                rows.add(record);
                record = new ArrayList<String>();
                lastColumnNumber = -1;
                if (rows.size() == callbackSize) {
                    doCallback(rows, false);
                    rows.clear();
                }
            }
        }

        public List<List<String>> getRows() {
            return rows;
        }

        /**
         * Captures characters only if a suitable element is open. Originally
         * was just "v"; extended for inlineStr also.
         */
        public void characters(char[] ch, int start, int length)
                throws SAXException {
            if (vIsOpen) {
                value.append(ch, start, length);
            }
        }

        /**
         * Converts an Excel column name like "C" to a zero-based index.
         *
         * @param name name
         * @return Index corresponding to the specified name
         */
        private int nameToColumn(String name) {
            int column = -1;
            for (int i = 0; i < name.length(); ++i) {
                int c = name.charAt(i);
                column = (column + 1) * 26 + c - 'A';
            }
            return column;
        }

        private String formatDateToString(Date date) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期
            return sdf.format(date);
        }
    }

    /**
     * Parses and shows the content of one sheet using the specified styles and
     * shared-strings tables.
     *
     * @param styles           StylesTable
     * @param strings          ReadOnlySharedStringsTable
     * @param sheetInputStream sheet输入流
     */
    private List<List<String>> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings, InputStream sheetInputStream)
            throws IOException, ParserConfigurationException, SAXException {

        InputSource sheetSource = new InputSource(sheetInputStream);
        SAXParserFactory saxFactory = SAXParserFactory.newInstance();
        SAXParser saxParser = saxFactory.newSAXParser();
        XMLReader sheetParser = saxParser.getXMLReader();
        XSSFSheetHandler handler = new XSSFSheetHandler(styles, strings);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
        return handler.getRows();
    }

    /**
     * Read the Excel 2003-2007
     *
     * @param path the path of the Excel
     * @return
     * @throws IOException
     */
    private List<List<String>> readXls(String path, int sheetNum) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // Read the Sheet
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheetNum - 1);
        return readXls(hssfSheet);
    }

    private List<List<String>> readXls(String path, String sheetName) throws IOException {
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        // Read the Sheet
        HSSFSheet hssfSheet = hssfWorkbook.getSheet(sheetName);
        return readXls(hssfSheet);
    }

    private List<List<String>> readXls(HSSFSheet hssfSheet) throws IOException {
        List<String> Strings;
        List<List<String>> list = new ArrayList<List<String>>();
        if (hssfSheet != null) {
            int totalRow = hssfSheet.getLastRowNum();
            for (int rowNum = 0; rowNum <= totalRow; rowNum++) {
                Strings = readRowXls(hssfSheet, rowNum);
                if (Strings != null) {
                    list.add(Strings);
                }
                if (list.size() == callbackSize) {
                    doCallback(list, false);
                    list.clear();
                }
            }
            doCallback(list, true);
        }
        return list;
    }

    /**
     * 读取一行excel数据
     *
     * @param rowNum
     * @throws IOException
     */
    public List<String> readRowXls(HSSFSheet hssfSheet, int rowNum)
            throws IOException {
        HSSFRow hssfRow = hssfSheet.getRow(rowNum);
        List<String> Strings = new ArrayList<String>();
        if (hssfRow != null) {
            int totalCell = hssfRow.getLastCellNum();
            for (int i = 0; i < totalCell; i++) {
                Strings.add(i, getValue(hssfRow.getCell(i)));
            }
            return Strings;
        }
        return null;
    }

    private String getValue(HSSFCell hssfCell) {
        if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue()).trim();
        } else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            String str = String.valueOf(hssfCell.getNumericCellValue()).trim();
            if (NumberUtils.isNumber(str)) {
                Number number = Float.parseFloat(str);
                int i = number.intValue();
                double d = Double.parseDouble(str);
                //如果是整数,可能会带小数，例如1.00，去掉小数
                if (i == d) {
                    return i + "";
                }
            }
            return str;
        } else {
            return String.valueOf(hssfCell.getStringCellValue()).trim();
        }
    }

    /**
     * get postfix of the path
     *
     * @param path 文件路径
     * @return 后缀名称
     */
    private static String getPostfix(String path) {
        if (path == null || EMPTY.equals(path.trim())) {
            return EMPTY;
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return EMPTY;
    }

    /**
     * 解读超大excel
     *
     * @throws IOException
     * @throws OpenXML4JException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    private void readBigXlsx(String path, String sheetName) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
        OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
        XSSFReader xssfReader = new XSSFReader(p);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (iter.hasNext()) {
            InputStream stream = iter.next();
            String sheetNameTemp = iter.getSheetName();
            if (sheetName.equals(sheetNameTemp)) {
                List<List<String>> list = processSheet(styles, strings, stream);
                stream.close();
                p.close();
                doCallback(list, true);
                return;
            }
        }
    }

    /**
     * 解读超大excel
     *
     * @throws IOException
     * @throws OpenXML4JException
     * @throws ParserConfigurationException
     * @throws SAXException
     */
    private List<List<String>> readBigXlsx(String path, int sheetNum) throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
        OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
        ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(p);
        XSSFReader xssfReader = new XSSFReader(p);
        StylesTable styles = xssfReader.getStylesTable();
        XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        int index = 0;
        while (iter.hasNext()) {
            if (index == sheetNum) {
                InputStream stream = iter.next();
                List<List<String>> list = processSheet(styles, strings, stream);
                stream.close();
                p.close();
                doCallback(list, true);
                return list;
            }
            index++;
        }
        return new ArrayList<List<String>>();
    }

    /**
     * 解读excel
     *
     * @param path      文件路径
     * @param sheetName sheet索引
     * @throws Exception
     */
    public void readExcel(String path, String sheetName) throws Exception {
        if (judgeReadType(path) == 1) {
            readXls(path, sheetName);
        } else if (judgeReadType(path) == 2) {
            readBigXlsx(path, sheetName);
        }
    }

    private int judgeReadType(String path) throws Exception {
        if (!(path == null || EMPTY.equals(path))) {
            String postfix = getPostfix(path);
            if (!EMPTY.equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return 1;
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return 2;
                }
            } else {
                LOGGER.warn(path + NOT_EXCEL_FILE);
            }
        }
        return 0;
    }

    private void doCallback(List<List<String>> list, boolean last) {
        if (callback != null) {
            callback.callback(list, first, last);
        }
        if (first) {
            first = false;
        }
    }

    /**
     * 解读excel
     *
     * @param path     文件路径
     * @param sheetNum sheet索引
     * @throws Exception
     */
    public List<List<String>> readExcel(String path, int sheetNum) throws Exception {
        if (judgeReadType(path) == 1) {
            return readXls(path, sheetNum);
        } else if (judgeReadType(path) == 2) {
            return readBigXlsx(path, sheetNum);
        }
        return new ArrayList<List<String>>();
    }

}